-- 1.
-- Crie o esquema OR referente ao sistema acima, usando o SGBD Oracle.


--- ====== Questão 2 ======
--- Crie uma stored procedure, usando PL/SQL, 
--- que receba como parâmetros o nome do cliente, o código de um cliente e uma data, 
--- e imprima o histórico de produtos e serviçoscomprados a partir desta data.

DROP procedure imprimeHistorico;

CREATE OR REPLACE PROCEDURE imprimeHistorico( nomeCliente IN cliente_tab.nome_completo%TYPE,
                                              codigoCliente IN cliente_tab.codigo%TYPE,
                                              dataInicio IN venda_tab.vend_data_venda%TYPE) AS
    nomeItem varchar(80);
    precoItem number(7,2);
    quantidadeItem int;
    data_venda Date;
     
    Cursor c is select vendas.itemnome, vendas.preco, vendas.quantidade, vendas.data
    from (SELECT v.vend_cliente.codigo as codigo, iv.itve_item.item_nome as itemnome, v.vend_data_venda as data, iv.itve_quantidade as quantidade, iv.itve_preco as preco
          FROM Venda_Tab v, table(v.vend_itens_vendidos) iv
          UNION 
          SELECT v.vend_cliente.codigo as codigo, iv.itve_item.item_nome as itemnome, v.vend_data_venda as data, iv.itve_quantidade as quantidade, iv.itve_preco as preco
          FROM Venda_Prestacao_Tab v, table(v.vend_itens_vendidos) iv
          ) vendas
    where vendas.codigo = codigoCliente AND vendas.data >= dataInicio;
begin
    DBMS_OUTPUT.put_line('Nome do Cliente: ' || nomeCliente);
    open c;
    loop
        fetch c into nomeItem, precoItem, quantidadeItem, data_venda;
        exit when c%notfound;
        DBMS_OUTPUT.put_line('Nome do item: ' || nomeItem);
        DBMS_OUTPUT.put_line('Preco do item: ' || precoItem);
        DBMS_OUTPUT.put_line('Quantidade do item: ' || quantidadeItem);
        DBMS_OUTPUT.put_line('Data de compra do item: ' || data_venda);
        DBMS_OUTPUT.put_line('');
     end loop;
     close c;
end;
/

begin
    imprimeHistorico('Maria de Lourdes', 1, to_date('2011/11/09', 'yyyy/mm/dd'));
end;
/

--- ======Questão 3======
--- Crie uma stored procedure, usando PL/SQL, que receba como parâmetros o nome de
--- um bairro e uma data, e imprima o TOP 10 das categorias de produtos mais vendidos
--- para este bairro a partir desta data.

DROP PROCEDURE imprimeMaisVendidos;

CREATE OR REPLACE PROCEDURE imprimeMaisVendidos(nomeBairro IN Cliente_Tab.endereco.bairro%TYPE, dataInicio IN Venda_Tab.vend_data_venda%TYPE) AS
    nomeItem varchar(80);
    quantidade int;
    CURSOR ccursor IS 
        SELECT c.item_nome, sum(vendas.quantidade) as quantidades
        FROM Cosmetico_Tab c, (SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod, v.vend_data_venda as data, iv.itve_quantidade as quantidade
                               FROM Venda_Tab v, table(v.vend_itens_vendidos) iv
                               UNION 
                               SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod, v.vend_data_venda as data, iv.itve_quantidade as quantidade
                               FROM Venda_Prestacao_Tab v, table(v.vend_itens_vendidos) iv
                               ) vendas
        WHERE vendas.bairro = nomeBairro AND vendas.itemCod = c.item_codigo AND vendas.data >= dataInicio
        GROUP BY c.item_nome
        ORDER BY quantidades desc;
 
   CURSOR hcursor IS 
      SELECT h.item_nome, sum(vendas_h.quantidade) as quantidades_h
      FROM Prod_Higiene_Pessoal_tab h, (SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod, v.vend_data_venda as data, iv.itve_quantidade as quantidade
                                        FROM Venda_Tab v, table(v.vend_itens_vendidos) iv
                                        UNION 
                                        SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod, v.vend_data_venda as data, iv.itve_quantidade as quantidade
                                        FROM Venda_Prestacao_Tab v, table(v.vend_itens_vendidos) iv
                                        ) vendas_h
      WHERE vendas_h.bairro = nomeBairro AND vendas_h.itemCod = h.item_codigo AND vendas_h.data >= dataInicio
      GROUP BY h.item_nome
      ORDER BY quantidades_h desc;

   CURSOR naoGenericocursor IS
      SELECT rng.item_nome, sum(vendas.quantidade) as quantidade
      FROM Remedio_Nao_Generico_Tab rng, (SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod, v.vend_data_venda as data, iv.itve_quantidade as quantidade
                                          FROM Venda_Tab v, table(v.vend_itens_vendidos) iv
                                          UNION 
                                          SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod, v.vend_data_venda as data, iv.itve_quantidade as quantidade 
                                          FROM Venda_Prestacao_Tab v, table(v.vend_itens_vendidos) iv
                                          ) vendas
      WHERE vendas.bairro = nomeBairro AND vendas.itemCod = rng.item_codigo AND vendas.data >= dataInicio
      GROUP BY rng.item_nome
      ORDER BY quantidade desc;
      
   CURSOR genericoCursor is 
      SELECT rg.item_nome, sum(vendas.quantidade) as quantidade
      FROM Remedio_Generico_Tab rg, (SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod, v.vend_data_venda as data, iv.itve_quantidade as quantidade
                                     FROM Venda_Tab v, table(v.vend_itens_vendidos) iv
                                     UNION 
                                     SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod, v.vend_data_venda as data, iv.itve_quantidade as quantidade
                                     FROM Venda_Prestacao_Tab v, table(v.vend_itens_vendidos) iv
                                     ) vendas
      WHERE vendas.bairro = nomeBairro AND vendas.itemCod = rg.item_codigo AND vendas.data >= dataInicio
      GROUP BY rg.item_nome
      ORDER BY quantidade desc;
      
begin
    DBMS_OUTPUT.put_line('Nome do bairro: ' || nomeBairro);
    DBMS_OUTPUT.put_line('Cosmeticos mais vendidos: ');
    open ccursor ;
    for i in 1..10 loop
        fetch ccursor into nomeItem, quantidade;
        exit when ccursor%notfound;
    DBMS_OUTPUT.put_line(i || ' - Nome do item: ' || nomeItem || ' - ' || quantidade);
    end loop;
    close ccursor;
    DBMS_OUTPUT.put_line('Produtos de higiene pessoal mais vendidos: ');
    open hcursor ;
    for i in 1..10 loop
        fetch hcursor into nomeItem,quantidade;
        exit when hcursor%notfound;
        DBMS_OUTPUT.put_line(i || ' - Nome do item: ' || nomeItem || ' - ' || quantidade);
    end loop;
    close hcursor;
    DBMS_OUTPUT.put_line('Remedios');
    DBMS_OUTPUT.put_line('Nao-genericos mais vendidos: ');
    open naoGenericocursor;
    for i in 1..10 loop
        fetch naoGenericocursor into nomeItem,quantidade;
        exit when naoGenericocursor%notfound;
        DBMS_OUTPUT.put_line(i || ' - Nome do item: ' || nomeItem || ' - ' || quantidade);
    end loop;
    close naoGenericocursor;
    DBMS_OUTPUT.put_line('Genericos mais vendidos: ');
    open genericocursor;
    for i in 1..10 loop
        fetch genericocursor into nomeItem,quantidade;
        exit when genericocursor%notfound;
        DBMS_OUTPUT.put_line(i || ' - Nome do item: ' || nomeItem || ' - ' || quantidade);
    end loop;
    close genericocursor;

end;
/

begin
    imprimemaisvendidos('Bodocongo',to_date('2010/10/10','YYYY/MM/DD'));
end;
/

-- 4.
-- Crie uma stored procedure que imprima na tela os produtos de uma 
-- determinada categoria (passada como parâmetro, exemplo: cosméticos) 
-- que foram compradas por clientes do bairro Bodocongó.

drop procedure imprimeProdutosPorCategoria;

   CREATE OR REPLACE PROCEDURE imprimeProdutosPorCategoria(categoria IN varchar) AS

CURSOR ccursor IS 
SELECT c.item_nome FROM Cosmetico_Tab c, (SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod 
                                          FROM Venda_Tab v, table(v.vend_itens_vendidos) iv
                                          UNION 
                                          SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod 
                                          FROM Venda_Prestacao_Tab v, table(v.vend_itens_vendidos) iv
                                          ) vendas
WHERE vendas.bairro = 'Bodocongo' AND vendas.itemCod = c.item_codigo;
 
CURSOR hcursor IS 
SELECT h.item_nome 
FROM Prod_Higiene_Pessoal_tab h, (SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod 
                                          FROM Venda_Tab v, table(v.vend_itens_vendidos) iv
                                          UNION 
                                          SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod 
                                          FROM Venda_Prestacao_Tab v, table(v.vend_itens_vendidos) iv
                                          ) vendas
WHERE vendas.bairro = 'Bodocongo' AND vendas.itemCod = h.item_codigo;

CURSOR rcursor IS 
    SELECT rng.item_nome 
FROM Remedio_Nao_Generico_Tab rng, (SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod 
                                                             FROM Venda_Tab v, table(v.vend_itens_vendidos) iv
                                                             UNION 
                                                             SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod 
                                                             FROM Venda_Prestacao_Tab v, table(v.vend_itens_vendidos) iv
                                                             ) vendas
    WHERE vendas.bairro = 'Bodocongo' AND vendas.itemCod = rng.item_codigo
    UNION 
    SELECT rg.item_nome FROM Remedio_Generico_Tab rg, (SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod 
                                                       FROM Venda_Tab v, table(v.vend_itens_vendidos) iv
                                                       UNION 
                                                       SELECT v.vend_cliente.endereco.bairro as bairro, iv.itve_item.item_codigo as itemCod 
                                                       FROM Venda_Prestacao_Tab v, table(v.vend_itens_vendidos) iv
                                                       ) vendas
    WHERE vendas.bairro = 'Bodocongo' AND vendas.itemCod = rg.item_codigo;

nome varchar(100);

BEGIN
 IF categoria = 'cosmetico' THEN 
      open ccursor;
          loop
              fetch ccursor into nome;
              exit when ccursor%notfound;
              DBMS_OUTPUT.put_line('Nome: ' || nome);

          end loop;
      close ccursor;
  ELSIF categoria = 'higiene' THEN
      open hcursor;
          loop
              fetch hcursor into nome;
              exit when hcursor%notfound;
              DBMS_OUTPUT.put_line('Nome: ' || nome);
          end loop;
      close hcursor;  
  ELSE
      open rcursor;
          loop
              fetch rcursor into nome;
              exit when rcursor%notfound;
              DBMS_OUTPUT.put_line('Nome: ' || nome);
          end loop;
      close rcursor; 
  END IF;
 
END;
/

begin
imprimeProdutoPorCategoria('higiene');
end;

-- 5.
-- Crie uma stored procedure que mostre uma lista com CPF, nome, 
-- emails e total de compras dos clientes.

drop procedure informacoesClientes;

CREATE OR REPLACE PROCEDURE informacoesClientes AS

CURSOR ccursor IS 
SELECT DISTINCT vendasTotal.clienteCod, COUNT(vendasTotal.clienteCod) as compras
FROM (SELECT DISTINCT v.vend_codigo, v.vend_cliente.codigo as clienteCod
      FROM Venda_Tab v
      UNION
      SELECT DISTINCT v.vend_codigo, v.vend_cliente.codigo as clienteCod
      FROM Venda_Prestacao_Tab v) vendasTotal
      GROUP BY vendasTotal.clienteCod
      ORDER BY compras;

CURSOR clienteCursor IS
SELECT c.nome_completo, c.CPF, c.telefones, c.codigo
FROM Cliente_Tab c;

compras int;
clienteCod number;
codigo number;
nomeCliente varchar(100);
cpf varchar(11);
telefones Lista_Telefone;
indice number := 1;
codigoPais char(2);
DDD char(3);
numero char(8);
telefone Telefone_Type;
BEGIN
     open ccursor;
          loop
              fetch ccursor into clienteCod, compras;
              exit when ccursor%notfound;
              open clienteCursor;
                  loop
                      fetch clienteCursor into nomeCliente, cpf, telefones, codigo;
                      exit when clienteCursor%notfound;
                      IF clienteCod = codigo THEN 
                          DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');
                          DBMS_OUTPUT.PUT_LINE('Nome: ' || nomeCliente);
                          DBMS_OUTPUT.PUT_LINE('CPF: ' || cpf);
                          indice := telefones.first;
                              loop
                                  exit when indice is null;
                                  telefone := telefones(indice);
                                  codigoPais := telefone.codigo_pais;
                                  DDD := telefone.DDD;
                                  numero := telefone.numero;
                                  DBMS_OUTPUT.PUT_LINE('Telefone' || indice || ': ' || '+' || codigoPais || DDD || numero);
                                  indice := telefones.next(indice);
                              end loop; 
                        DBMS_OUTPUT.PUT_LINE('Quantidade Comprado: ' || compras);
                      END IF;
                  end loop;
               close clienteCursor;
          end loop;
      close ccursor;
END;
/
begin
informacoesClintes;
end;

-- 6.
-- Crie um método construtor para cliente que receba o código do cliente, 
-- o nome do cliente e todos os demais atributos que não aceitam valor 
-- nulo, deixando o resto dos campos com NULL.

ALTER TYPE Cliente_Type DROP CONSTRUCTOR FUNCTION Cliente_Type(
        c_codigo           number,
        c_nome_completo    varchar,
        c_RG               varchar,
        c_CPF              varchar
    )RETURN SELF AS RESULT cascade;
    

ALTER TYPE Cliente_Type ADD CONSTRUCTOR FUNCTION Cliente_Type(
        c_codigo           number,
        c_nome_completo    varchar,
        c_RG               varchar,
        c_CPF              varchar
    )RETURN SELF AS RESULT cascade;
    
create or replace type body Cliente_Type as 
    CONSTRUCTOR FUNCTION Cliente_Type(
        c_codigo           number,
        c_nome_completo    varchar,
        c_RG               varchar,
        c_CPF              varchar
    ) RETURN SELF AS RESULT IS


        BEGIN 
            SELF.codigo := c_codigo;
            SELF.nome_completo := c_nome_completo;
            SELF.RG := c_RG;
            SELF.CPF := c_CPF;
            SELF.naturalidade := null;
            SELF.nacionalidade := null;
            SELF.sexo := null;
            SELF.data_nascimento := null;
            SELF.telefones := null;
            SELF.endereco := null;
        END;
END;

insert into cliente_tab(codigo,nome_completo,rg, cpf) values(100020, 'Telles','2689368','05139568403');

-- 7.
-- Crie um trigger, que mostre na tela, um aviso de advertência com os 
-- dados de venda repetida, toda vez que um cliente estiver tentando 
-- comprar um remédio pela segunda vez, quando o remédio é tarja preta
-- e precisa de indicação médica.

-- 8.
-- Crie um método de instância SQL no tipo Empregado (Vendedor) que 
-- realize a mudança dos juros a prazo de todas as vendas, sendo o valor
-- dos novos juros passado como parâmetro.

ALTER TYPE Vendedor_Type DROP MEMBER PROCEDURE alteraJuros(juros IN number) CASCADE;

ALTER TYPE Vendedor_Type ADD MEMBER PROCEDURE alteraJuros(juros IN number) CASCADE;

CREATE OR REPLACE TYPE BODY Vendedor_Type AS MEMBER PROCEDURE alteraJuros(juros IN number) IS
   cursor c_vendas is SELECT * FROM Venda_Prestacao_Tab v;
   venda c_vendas%ROWTYPE;
   BEGIN 
       OPEN c_vendas;
	   loop
	       fetch c_vendas into venda;
		   exit when c_vendas%NOTFOUND;
		   UPDATE Venda_Prestacao_Tab
		   SET vepr_taxa_juros = juros
		   WHERE vend_codigo = venda.vend_codigo;
		   end loop;
		   close c_vendas;
   END;
END;
/

declare
    v Vendedor_Type;
BEGIN
    SELECT VALUE(c)
    INTO v
    FROM Vendedor_Tab c
    WHERE c.codigo = 1;
    v.alteraJuros(1); 
END;

-- 9.
-- Crie um método estático SQL para o tipo Cliente, que exiba o que ainda
-- é devido a prazo por todos os clientes.

ALTER TYPE Cliente_Type DROP STATIC PROCEDURE divida_prazo CASCADE;

ALTER TYPE Cliente_Type ADD STATIC PROCEDURE divida_prazo CASCADE;

CREATE OR REPLACE TYPE BODY Cliente_Type AS STATIC PROCEDURE divida_prazo IS
   total number;
   BEGIN 
       SELECT SUM(v.vend_valor_venda)
       INTO total
       FROM Venda_Prestacao_Tab v, Cliente_Tab c 
       WHERE v.vend_cliente.codigo = c.codigo;
       DBMS_OUTPUT.put_line('Total divida a prazo: R$ ' ||total);
   END;
END;
/

BEGIN
    Cliente_Type.divida_prazo();
END;

-- 10.
-- Crie um método de instância no tipo fornecedor que mostre os produtos
-- fornecidos (todas as informações devem ser traduzidas de português para
-- inglês).

ALTER TYPE Fornecedor_Type DROP MEMBER PROCEDURE produtosFornecidos(SELF IN Fornecedor_Type) CASCADE;

ALTER TYPE Fornecedor_Type ADD MEMBER PROCEDURE produtosFornecidos(SELF IN Fornecedor_Type) CASCADE;

CREATE OR REPLACE TYPE BODY Fornecedor_Type AS MEMBER PROCEDURE produtosFornecidos(SELF IN Fornecedor_Type) IS
   cursor c_produtos is (SELECT c.prod_name FROM Cosmetico_Tab c WHERE c.prod_fornecedor.codigo = SELF.codigo
							UNION
							SELECT p.prod_name FROM Prod_Higiene_Pessoal_Tab p WHERE p.prod_fornecedor.codigo = SELF.codigo
							UNION
							SELECT rn.prod_name FROM Remedio_Nao_Generico_Tab rn WHERE rn.prod_fornecedor.codigo = SELF.codigo
							UNION
							SELECT r.prod_name FROM Remedio_Generico_Tab r WHERE r.prod_fornecedor.codigo = SELF.codigo);
   produto c_produtos%ROWTYPE;
   BEGIN 
       OPEN c_produtos;
	   loop
	       fetch c_produtos into produto;
		   exit when c_produtos%NOTFOUND;
		   DBMS_OUTPUT.put_line('Product: ' || produto.prod_name);
		   end loop;
		   close c_produtos;
   END;
END;
/

declare
    f Fornecedor_Type;
BEGIN
    SELECT VALUE(b)
    INTO f
    FROM Fornecedor_Tab b
    WHERE b.codigo = 1;
    f.produtosFornecidos(); 
END;

-- 11.
-- Listar a quantidade de produtos vendidos por tipo (cosméticos, remédios, 
-- higiene pessoal) e o total em dinheiro arrecadado em ordem crescente.

WITH todosProdutosVendidos AS (
SELECT nt1.* FROM Venda_Tab v1, TABLE(v1.vend_itens_vendidos) nt1
UNION ALL
SELECT nt2.* FROM Venda_Prestacao_Tab v2, TABLE(v2.vend_itens_vendidos) nt2)
SELECT 'Cosmeticos' as Produto, COUNT(*) AS Quantidade, SUM(pV.itve_preco * pV.itve_quantidade) AS ValorTotal
FROM todosProdutosVendidos pV
WHERE DEREF(pV.itve_item) IS OF (Cosmetico_type)
UNION
SELECT 'Remedios' as Produto, COUNT(*) AS Quantidade, SUM(pV.itve_preco * pV.itve_quantidade) AS ValorTotal
FROM todosProdutosVendidos pV
WHERE DEREF(pV.itve_item) IS OF (Remedio_type)
UNION
SELECT 'Higiene Pessoal' as Produto, COUNT(*) AS Quantidade, SUM(pV.itve_preco * pV.itve_quantidade) AS ValorTotal
FROM todosProdutosVendidos pV
WHERE DEREF(pV.itve_item) IS OF (Prod_Higiene_Pessoal_Type)
ORDER BY ValorTotal ASC

-- 12.
-- Listar todos os serviços vendidos em janeiro de 2011 agrupados pelos 
-- nomes dos farmacêuticos responsáveis.

select  deref(iv.ITVE_ITEM).item_nome as name 
from Venda_Tab v,  table(VEND_ITENS_VENDIDOS) iv
where deref(iv.ITVE_ITEM) IS OF(Servico_type) and to_char(v.vend_data_venda, 'mm-yyyy') = '01-2011'

-- 13.
-- Listar o empregado que é o único que possui uma determinada especialidade.

with  farmaceutico_especialidade as (
select f.nome_completo as nome, s.COLUMN_VALUE as especialidade
from farmaceutico_tab f, TABLE(CAST(especialidades AS LISTA_ESPECIALIDADE)) s
)select nome, especialidade  
from farmaceutico_especialidade fe
where (select count(*)from farmaceutico_especialidade  where especialidade = fe.especialidade) = 1 



-- 14.
-- Listar o produto mais vendido em 2010 para clientes idosos (idade > 60).

with produto_quantidade_venda as
(select nome , sum(qn)
 from (select  deref(iv.ITVE_ITEM).item_nome as nome, iv.ITVE_QUANTIDADE as qn,
deref(v.VEND_CLIENTE).data_nascimento as dt_nascimento,
v.vend_data_venda as data
from Venda_Tab v,  table(VEND_ITENS_VENDIDOS) iv
where deref(iv.ITVE_ITEM) IS OF(Produto_type)
union
select  deref(iv.ITVE_ITEM).item_nome as nome, iv.ITVE_QUANTIDADE as qn,
deref(v.VEND_CLIENTE).data_nascimento as dt_nascimento,
v.vend_data_venda as data
from Venda_Prestacao_Tab v,  table(VEND_ITENS_VENDIDOS) iv
where deref(iv.ITVE_ITEM) IS OF(Produto_type)
)
where dt_nascimento < ADD_MONTHS (sysdate, -720)
and to_char(data, 'yyyy') = '2010'
group by nome)
select name
from produto_quantidade_venda
where qn = (select max(qn) from produto_quantidade_venda)

-- 15.
-- Listar todos os clientes com parcelas pendentes, com o valor total pago das
-- parcelas e a data do último pagamento pendente ordenados em ordem decrescente
-- do valor total ainda devido.


select nome,  parcelas_pagas * valor_parcela as vator_total, add_months(data, parcelas_pagas) as ultima_parcela
from 
(select  deref(vp.VEND_CLIENTE).nome_completo as nome, 
MONTHS_BETWEEN(round(sysdate, 'mm'), round(vp.VEND_DATA_Venda, 'mm')) as parcelas_pagas, vp.VEPR_NUMERO_PARCELAS as parcelas,
vp.vepr_valor_parcela as valor_parcela,
vp.VEND_DATA_Venda as data
from Venda_Prestacao_Tab vp
)
where parcelas > parcelas_pagas
order by (parcelas -parcelas_pagas) * valor_parcela desc